﻿using System;
using System.Collections.Generic;
using System.Text;
using SQLDMO;
using System.IO;
using System.Text.RegularExpressions;

namespace dbscripts
{
	class Program
	{
		static void Main(string[] args)
		{
			try
			{
				ScriptGenerator g = new ScriptGenerator();
				g.Run(args);
			}
			catch (Exception exc)
			{
				Console.Error.WriteLine(exc.ToString());
			}
		}
	}

	class ScriptGenerator
	{
		private string connstr;
		private string host;
		private string database;
		private string user;
		private string password;
		private string rcfile = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + "\\.dbscriptsrc";
		private bool integratedSecurity = false;
		private bool runTests = false;
		private bool showUsage = false;
		private bool exportData = false;
		private bool exportDataOnly = false;

		private Dictionary<string, string> GetConnStrs(List<string> list)
		{
			Dictionary<string, string> res = new Dictionary<string, string>();
			bool skip = true;
			foreach (string s in list)
			{
				if (s.StartsWith("[connstrs]"))
				{
					skip = false;
					continue;
				}

				if (skip == true || s.StartsWith("#") || s.Trim().Length == 0)
				{
					continue;
				}

				if (s.StartsWith("["))
				{
					break;
				}

				string[] cols = s.Split(" |".ToCharArray(), 2, StringSplitOptions.RemoveEmptyEntries);
				res.Add(cols[0], cols[1]);
			}

			return res;
		}

		private string GetPassword(string hint)
		{
			if (hint.StartsWith("encrypted:"))
			{
				string path = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + "\\passwd\\" + hint.Replace("encrypted:", "");
                FileAttributes attrs = File.GetAttributes(path);
                if (!((FileAttributes.Encrypted & attrs) == FileAttributes.Encrypted))
                {
                    Console.WriteLine("Warning: password file {0} is not encrypted", path);
                }

				if (File.Exists(path))
				{
					return File.ReadAllText(path);
				}
			}

			return hint;
		}

		private void ParseParams(string[] args)
		{
			int idx = 0;
			while (idx < args.Length)
			{
				if (args[idx] == "--connstr" && idx < args.Length - 1)
				{
					connstr = args[++idx];
				}
				else if (args[idx] == "--tests")
				{
					runTests = true;
				}
				else if (args[idx] == "--data")
				{
					exportData = true;
				}
				else if (args[idx] == "--dataonly")
				{
					exportData = exportDataOnly = true;
				}
				else if (args[idx] == "--config" && idx < args.Length - 1)
				{
					rcfile = args[++idx];
				}
				else if (args[idx] == "--help" || args[idx] == "-h" || args[idx] == "/?" || args[idx] == "-?")
				{
					showUsage = true;
				}
				else
				{
					showUsage = true;
				}

				idx++;
			}

			List<string> rclines = new List<string>();

			if (string.IsNullOrEmpty(connstr) && File.Exists(rcfile))
			{
				rclines = new List<string>(File.ReadAllText(rcfile).Split("\n\r".ToCharArray(), StringSplitOptions.RemoveEmptyEntries));
				Dictionary<string, string> connstrs = GetConnStrs(rclines);
				if (connstrs.Count > 0)
				{
					Console.WriteLine("Available connection strings:");
					List<string> keys = new List<string>(connstrs.Keys);
					for (int i = 0; i < keys.Count; i++)
					{
						Console.WriteLine("{0} {1}", i + 1, keys[i]);
					}
					Console.WriteLine("Enter connection number: ");
					string s = Console.In.ReadLine();
					if (!string.IsNullOrEmpty(s))
					{
						int num = int.Parse(s) - 1;
						if (num >= 0 && num < connstrs.Count)
						{
							connstr = connstrs[keys[num]];
						}
					}
				}
			}
			else
			{
				// generate default file
				StringBuilder sb = new StringBuilder();
				sb.AppendLine("[connstrs]");
				sb.AppendLine("#local.sampledb1 Data Source=localhost\\instance1,1234;Initial Catalog=dbname;User Id=dbuser;Password=password");
				sb.AppendLine("#local.sampledb2 Data Source=localhost\\instance1,1234;Initial Catalog=dbname;Integrated Security=SSPI");
				File.AppendAllText(rcfile, sb.ToString());
				File.SetAttributes(rcfile, FileAttributes.Hidden);
			}

			if (string.IsNullOrEmpty(connstr))
			{
				Console.WriteLine("--connstr parameter is not specified. Using localhost");
				host = "localhost";
				integratedSecurity = true;
			}
			else
			{
				Console.WriteLine("connstr: " + connstr);

				Match m = new Regex("Data Source=([^;]*)").Match(connstr);
				if (m.Groups.Count > 1)
				{
					host = m.Groups[1].Value;
				}

				m = new Regex("Initial Catalog=([^;]*)").Match(connstr);
				if (m.Groups.Count > 1)
				{
					database = m.Groups[1].Value;
				}

				m = new Regex("User Id=([^;]*)").Match(connstr);
				if (m.Groups.Count > 1)
				{
					user = m.Groups[1].Value;
				}

				m = new Regex("Password=([^;]*)").Match(connstr);
				if (m.Groups.Count > 1)
				{
					password = GetPassword(m.Groups[1].Value);
				}

				integratedSecurity = connstr.Contains("Integrated Security=SSPI");

				if (string.IsNullOrEmpty(user) && string.IsNullOrEmpty(password))
				{
					integratedSecurity = true;
				}
			}
		}

		private static void Usage()
		{
			string[] usage = {

				"dbscripts -- export db creation scripts",
				"USAGE: dbscripts [OPTIONS]",
				"Options:",
				"    --connstr STR   -- connection string",
				"    --data          -- export schema and data",
				"    --dataonly      -- export data only",
				"    --config FILE   -- specify config file",
				"    --test          -- run internal tests (Debug)",
				"Example:",
				"    dbscripts --connstr \"Data Source=host\\instance1,1234;Initial Catalog=dbname;User Id=dbuser;Password=password",
				"    dbscripts --connstr \"Data Source=host\\instance1,1234;Initial Catalog=dbname;Integrated Security=SSPI",
				"Output directory is ./dbname-HOSTNAME/",
				"By default dbscripts reads .dbscriptrc file from home directory for connection strings"
			};
			
			foreach (string s in usage)
			{
				Console.WriteLine(s);
			}
		}

		private int RunTests()
		{
			int ret = 0;

			string[] args = {
								"--connstr",
								"Data Source=host\\instance1,1234;Initial Catalog=dbname;User Id=dbuser;Password=password"
							};

			ParseParams(args);

			if (host != "host\\instance1,1234")
			{
				Console.Error.WriteLine("ERROR: 'Data Source' parameter is not parsed");
				ret = 1;
			}
			if (database != "dbname")
			{
				Console.Error.WriteLine("ERROR: 'Initial Catalog' parameter is not parsed");
				ret = 1;
			}
			if (user != "dbuser")
			{
				Console.Error.WriteLine("ERROR: 'User Id' parameter is not parsed");
				ret = 1;
			}
			if (password != "password")
			{
				Console.Error.WriteLine("ERROR: 'Password' parameter is not parsed");
				ret = 1;
			}

			if (ret == 0)
			{
				Console.WriteLine("ParseParams1 - OK");
			}

			string[] args1 = {
								"--connstr",
								"Data Source=host\\instance2,1234;Initial Catalog=dbname2;Integrated Security=SSPI"
							};

			ParseParams(args1);

			if (host != "host\\instance2,1234")
			{
				Console.Error.WriteLine("ERROR: 'Data Source' parameter is not parsed");
				ret = 1;
			}
			if (database != "dbname2")
			{
				Console.Error.WriteLine("ERROR: 'Initial Catalog' parameter is not parsed");
				ret = 1;
			}
			if (!integratedSecurity)
			{
				Console.Error.WriteLine("ERROR: 'Integrated Security' parameter is not parsed");
				ret = 1;
			}

			if (ret == 0)
			{
				Console.WriteLine("ParseParams2 - OK");
			}

			return ret;

		}

		private _Database2 GetDatabase(Databases dbs)
		{
			foreach (_Database2 db in dbs)
			{
				if (db.SystemObject)
				{
					continue;
				}

				if (db.Name == database)
				{
					return db;
				}
			}

			foreach (_Database2 db in dbs)
			{
				if (db.SystemObject)
				{
					continue;
				}
				Console.WriteLine("{0} {1}", db.ID, db.Name);
			}

			Console.Write("Enter database ID: ");
			string id = Console.In.ReadLine();
			if (string.IsNullOrEmpty(id))
			{
				return null;
			}

			return (_Database2)dbs.ItemByID(int.Parse(id));
		}

		public int Run(string[] args)
		{
			ParseParams(args);

			if (showUsage)
			{
				Usage();
				return 1;
			}

			if (runTests)
			{
				return RunTests();
			}

			SQLServer2Class server = new SQLServer2Class();

			if (integratedSecurity)
			{
				server.LoginSecure = true;
				server.Connect(host);
			}
			else
			{
				server.Connect(host, user, password);
			}

			_Database2 sdb = GetDatabase(server.Databases);

			if (sdb == null)
			{
				Console.Error.WriteLine("Database {0} not found", database);
				return 1;
			}

			string outdir = ".\\" + sdb.Name + "-" + host.ToUpper() + "\\";
			if (!Directory.Exists(outdir))
			{
				Directory.CreateDirectory(outdir);
			}

			if (!exportDataOnly)
			{
				ExportScripts(sdb, outdir);
			}

			if (exportData)
			{
				ExportData(server, sdb, outdir);
			}

			return 0;
		}

		private static void ExportData(SQLServer2Class server, _Database2 sdb, string outdir)
		{
			const int OneMiB = 1024*1024;
			string dir = GetDir(outdir, "data");

            Console.WriteLine("Exporting data...");

			foreach (_Table2 o in sdb.Tables)
			{
				if (o.SystemObject)
				{
					continue;
				}

				string p = dir + o.Name + ".txt";

                if (File.Exists(p))
                {
                    Console.WriteLine("{0} -- skipped", o.Name);
                    continue;
                }

				string cols = "";
				foreach (_Column2 c in o.Columns)
				{
					cols += c.Name + "\t";
				}
				File.WriteAllText(p, cols.TrimEnd() + "\n");
				StringBuilder sb = new StringBuilder();

				QueryResults res = server.ExecuteWithResults("select * from " + sdb.Name + ".." + o.Name);

				for (int row = 1; row <= res.Rows; row++)
				{
					for (int col = 1; col <= res.Columns; col++)
					{
						sb.Append(res.GetColumnString(row, col));
						if (col < res.Columns)
						{
							sb.Append('\t');
						}
					}
					sb.Append('\n');
					if (sb.Length > OneMiB)
					{
						File.AppendAllText(p, sb.ToString());
						sb.Length = 0;
					}
				}

				if (sb.Length > 0)
				{
					File.AppendAllText(p, sb.ToString());
					sb.Length = 0;
				}

                Console.WriteLine("{0}", o.Name);
			}
		}

		private static void ExportScripts(_Database2 sdb, string outdir)
		{
			
			SQLDMO_SCRIPT_TYPE type =
				SQLDMO_SCRIPT_TYPE.SQLDMOScript_ToFileOnly |
				SQLDMO_SCRIPT_TYPE.SQLDMOScript_PrimaryObject |
				SQLDMO_SCRIPT_TYPE.SQLDMOScript_ClusteredIndexes |
				SQLDMO_SCRIPT_TYPE.SQLDMOScript_DatabasePermissions |
				SQLDMO_SCRIPT_TYPE.SQLDMOScript_TransferDefault |
				SQLDMO_SCRIPT_TYPE.SQLDMOScript_Triggers |
				SQLDMO_SCRIPT_TYPE.SQLDMOScript_Permissions |
				SQLDMO_SCRIPT_TYPE.SQLDMOScript_NonClusteredIndexes |
				SQLDMO_SCRIPT_TYPE.SQLDMOScript_Indexes |
				SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops |
				SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_All
				;

			type = type & ~SQLDMO_SCRIPT_TYPE.SQLDMOScript_IncludeHeaders;

			SQLDMO_SCRIPT2_TYPE type2 = 0;

			sdb.Script(type, outdir + "db.sql", type2);

			// tables
			{
				string dir = GetDir(outdir, "tables");

				foreach (_Table2 o in sdb.Tables)
				{
					if (o.SystemObject)
					{
						continue;
					}

					Console.WriteLine(o.Name);
					string p = dir + o.Name + ".sql";
					o.Script(type, p, null, type2);
					File.SetLastWriteTime(p, DateTime.Parse(o.CreateDate));
				}
			}

			// views
			{
				string dir = GetDir(outdir, "views");

				foreach (_View o in sdb.Views)
				{
					if (o.SystemObject)
					{
						continue;
					}

					Console.WriteLine(o.Name);
					string p = dir + o.Name + ".sql";
					o.Script(type, p, type2);
					File.SetLastWriteTime(p, DateTime.Parse(o.CreateDate));
				}
			}

			// rules
			{
				string dir = GetDir(outdir, "rules");

				foreach (_Rule o in sdb.Rules)
				{
					Console.WriteLine(o.Name);
					string p = dir + o.Name + ".sql";
					o.Script(type, p, type2);
					File.SetLastWriteTime(p, DateTime.Parse(o.CreateDate));
				}
			}
	
			// sps
			{
				string dir = GetDir(outdir, "sps");

				foreach (_StoredProcedure o in sdb.StoredProcedures)
				{
					Console.WriteLine(o.Name);
					string p = dir + o.Name + ".sql";
					o.Script(type, dir + o.Name + ".sql", type2);
					File.SetLastWriteTime(p, DateTime.Parse(o.CreateDate));
				}
			}

			// udfs
			{
				string dir = GetDir(outdir, "udfs");

				foreach (_UserDefinedFunction o in sdb.UserDefinedFunctions)
				{
					Console.WriteLine(o.Name);
					string p = dir + o.Name + ".sql";
					o.Script(type, dir + o.Name + ".sql", type2);
					File.SetLastWriteTime(p, DateTime.Parse(o.CreateDate));
				}
			}

			// users
			{
				string dir = GetDir(outdir, "users");

				foreach (_User o in sdb.Users)
				{
					if (o.SystemObject)
					{
						continue;
					}
					string p = dir + o.Name + ".sql";
					Console.WriteLine(o.Name);
					o.Script(type, dir + o.Name + ".sql", type2);
				}
			}
		}
		
		static string GetDir(string baseDir, string name)
		{
			string dir = baseDir + name + "\\";
			if (!Directory.Exists(dir))
			{
				Directory.CreateDirectory(dir);
			}
			return dir;
		}
	}
}
